package zzj;

import java.sql.*;
import java.util.*;

public class SqliteDao {
//	public static void main(String args[]) {
//		List<HashMap<String, String>> rslst = new ArrayList<HashMap<String, String>>();
//		SqliteDao sd = new SqliteDao();
//		WhereIsMovie wim=new WhereIsMovie();
////		wim.getMovieFromPath("E:\\");
//		// String
//		// sql="insert into MOVIE_INFO(ID,MOVIE_TITTLE,MOVIE_FOLDER,MOVIE_FILE_NAME,MOVIE_FILE_PATH) VALUES ('1','机器猫','E:\\Movie\\机器猫 1080p','Stand.By.Me.Doraemon.2014.1080p.BluRay.x264.DTS-WiKi.mkv','E:\\Movie\\机器猫 1080p\\Stand.By.Me.Doraemon.2014.1080p.BluRay.x264.DTS-WiKi.mkv')";
//		// sd.execUpdate(sql);
//		String sql = "select * from MOVIE_INFO";
//		rslst = sd.execQuery(sql);
////		sd.delMovieInfo("E:\\Movie\\机器猫 1080p");
//		System.out.println(rslst.size());
//	}

	public Connection getDBConn() {
		Connection c = null;
		try {
			Class.forName("org.sqlite.JDBC");
			c = DriverManager.getConnection("jdbc:sqlite:db/movie.db");
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
		return c;
	}

	public void insertMovieInfo(Map<String, String> map) {
		String id = getUUID();
		String movieTittle = map.get("MOVIE_TITTLE").replace("'", "''");
		String movieFolder = map.get("MOVIE_FOLDER").replace("'", "''");
		String movieFileName = map.get("MOVIE_FILE_NAME").replace("'", "''");
		String movieFilePath = map.get("MOVIE_FILE_PATH").replace("'", "''");
		String volSerial=map.get("VOL_SERIAL");
		String volName=map.get("VOL_NAME");
		String sql = String
				.format("insert into MOVIE_INFO(ID,MOVIE_TITTLE,MOVIE_FOLDER,MOVIE_FILE_NAME,MOVIE_FILE_PATH,VOL_SERIAL,VOL_NAME) VALUES ('%s','%s','%s','%s','%s','%s','%s') ",
						id, movieTittle, movieFolder, movieFileName,
						movieFilePath,volSerial,volName);
//		System.out.println(sql);
		execUpdate(sql);
	}

	public void insertConf(String path) {
		WhereIsMovie wim=new WhereIsMovie();
		String id = getUUID();
		String volSerial=wim.getVolSerial(path);
		String volName=wim.getVolName(path);
		String sql = String.format(
				"insert into CONF(ID,PATH,VOL_SERIAL,VOL_NAME) VALUES ('%s','%s','%s','%s')", id, path.replace("'", "''"),volSerial,volName);
		
//		System.out.println(sql);
		execUpdate(sql);
	}

	public void delMovieInfo(String path, String volSerial) {
		String sql = String.format(
				"delete from MOVIE_INFO where MOVIE_FOLDER like '%s%%' and VOL_SERIAL='%s'", path,volSerial);
//		System.out.println(sql);
		execUpdate(sql);
	}

	public void delConf(String path, String volSerial) {
		String sql = String.format("delete from CONF where PATH='%s' and VOL_SERIAL='%s'", path.replace("'", "''"),volSerial);
		execUpdate(sql);
	}

	public void editConf(String id, String path) {
		String sql = String.format("update CONF set PATH='%s' where ID='%s' ",
				path, id);
		execUpdate(sql);

	}

	public int execUpdate(String sql) {
		Connection c = null;
		Statement stmt = null;
		int result = 0;
		try {
			c = this.getDBConn();
			stmt = c.createStatement();
			result = stmt.executeUpdate(sql);
			stmt.close();
			// c.commit();
			c.close();

		} catch (Exception e) {
			// TODO: handle exception
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);

		}
		return result;
	}

	public List<HashMap<String, String>> execQuery(String sql) {
		Connection c = null;
		Statement stmt = null;
		ResultSetMetaData cols = null;

		ResultSet rs = null;
		List<HashMap<String, String>> result = new ArrayList<HashMap<String, String>>();
		try {
			c = this.getDBConn();
			stmt = c.createStatement();
			rs = stmt.executeQuery(sql);
			cols = rs.getMetaData();
			cols.getColumnCount();
			while (rs.next()) {
				HashMap<String, String> map = new HashMap<String, String>();
				for (int i = 1; i <= cols.getColumnCount(); i++) {
					map.put(cols.getColumnName(i),
							rs.getString(cols.getColumnName(i)));
				}
				result.add(map);

			}
			rs.close();
			stmt.close();
			c.close();

		} catch (Exception e) {
			// TODO: handle exception
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);

		}
		return result;
	}

	public String getUUID() {
		return UUID.randomUUID().toString().replace("-", "").toUpperCase();
	}

	public List<HashMap<String, String>> searchMovieInfo(String name) {
		// TODO Auto-generated method stub
		String sql=String.format("select * from MOVIE_INFO where MOVIE_TITTLE like '%%%s%%' or  MOVIE_FILE_NAME like '%%%s%%'",name,name);
		List<HashMap<String, String>> resultList=new ArrayList<HashMap<String,String>>();
		resultList=execQuery(sql);
		return resultList;
		
	}
	public List<HashMap<String, String>> getConfData(){
		String sql="select * from CONF";
		List<HashMap<String, String>> resultList=new ArrayList<HashMap<String,String>>();
		resultList=execQuery(sql);
		return resultList;
	}
}